# coding:utf-8
import xlrd
# pip install xlsxwriter
import xlsxwriter

# # 生成的excel的名称
# excel = xlsxwriter.Workbook('write1.xlsx')
# # 生成的excel中工作簿的名称
# book = excel.add_worksheet("study")
#
title = ['姓名', '性别', '年龄', '成绩', '等级']
print(list(enumerate(title)))
# for index,data in enumerate(title):
#     # 0 代表excel中的第一行  index代表第几列
#     book.write(0,index,data)
# excel.close()

# 读取excel表格指定工作簿内容
def readExcel():
    result = []
    excel = xlrd.open_workbook('study.xlsx')
    book = excel.sheet_by_name('学生手册')
    for i in book.get_rows():
        content = []
        for j in i:
            content.append(j.value)
        result.append(content)
    return  result


# 写入excel表格指定工作簿内容
def writeExcel(content):

    # 写入excel表格
    excel = xlsxwriter.Workbook('write.xlsx')

    # 写入excel表格的study工作簿
    book = excel.add_worksheet('study')

    for index,data in enumerate(content):
        print(data)
        for sub_index,sub_data in enumerate(data):
            book.write(index,sub_index,sub_data)

    # 写入excel表格的学生等级工作簿
    book1 = excel.add_worksheet('学生等级')
    # 写入数据
    data = [
        ['优秀', '良好', '中', '差'],
        [1100, 2000, 1000, 900]
    ]
    book1.write_column('A1',data[0])
    book1.write_column('B1',data[1])

    # 写入图表
    chart = excel.add_chart({'type':'column'}) #柱状图
    chart.add_series({
        'categories':'=学生等级!$A1:$A4',
        'values':'=学生等级!$B1:$B4',
        'name':'成绩占比'
    })
    chart.set_title({'name':'成绩占比图表'})
    book1.insert_chart('A10',chart)


    excel.close()


if __name__ == '__main__':
    result = readExcel()
    print(result)
    writeExcel(result)
